let server = require("express")();
let url = require("url");
let mysql = require("mysql");
let expressStatic = require("express-static");
// let ejs = require("ejs");
let port = 82;
let sql = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'school'
});
sql.connect();

// 查库
server.get(`/index`, (req, res) => {
    sql.query(`SELECT * FROM students`, (error, data) => {
        if (error) {
            console.log(error);
            res.end("error")
        } else {
            console.log("返回数据成功");
            res.end(JSON.stringify(data))
        }
    })
})

//登录库
server.get(`/sign`, (req, res) => {
    sql.query(`SELECT * FROM signin`, (error, data) => {
        if (error) {
            console.log(error);
            res.end("error")
        } else {
            console.log("返回数据成功");
            res.end(JSON.stringify(data))
        }
    })
})

// 注册
server.get("/regi",(request,response)=>{

	let username = url.parse(request.url,true).query.username;
	let password = url.parse(request.url,true).query.password;

	if(username && password){
		sql.query(`SELECT * FROM signin WHERE username="${username}"`,(error,data)=>{
			if(data.length){
				response.end(`repeat`);
			}
			else{
				// 写入新用户数据
				sql.query(`INSERT INTO si (username,password) VALUES ("${username}","${password}")`,(error,data)=>{
					if(error){
						response.end("database error");
						console.log(error)
					}
					else{
						// 反解析后向前端返回JSON字符串
						response.end(`success`)
					}
				})
			}

		})

	}

	else{
		response.end("error")
	}
})

//删除
server.get("/delete", (req, res) => {
    let Id = url.parse(req.url, true).query.id;
    sql.query(`DELETE  FROM students WHERE id="${Id}"`, (error, data) => {
        if (error) {
            console.log(error)
            res.end("error")
        } else {
            console.log("success");
            res.end("success")
        }
    })
});

// 添加
server.get("/add", (req, res) => {
    let newname = url.parse(req.url, true).query.name;
    let newage = url.parse(req.url, true).query.age;
    let newcity = url.parse(req.url, true).query.city;
    let newsex = url.parse(req.url, true).query.sex;
    sql.query(`INSERT INTO students (name,sex,age,city) VALUES ("${newname}","${newsex}","${newage}","${newcity}")`, (error, data) => {
        if (error) {
            console.log(error)
            res.end("error")
        } else {
            console.log("success");
            res.end("success")
        }
    })
});
// 修改
server.get("/change", (req, res) => {
    let newname = url.parse(req.url, true).query.name;
    let newage = url.parse(req.url, true).query.age;
    let newcity = url.parse(req.url, true).query.city;
    let newsex = url.parse(req.url, true).query.sex;
    let id = url.parse(req.url, true).query.id;
    sql.query(`UPDATE students SET name='${newname}',age='${newage}',city='${newcity}',sex='${newsex}' WHERE id='${id}' `, (error, data) => {
        if (error) {
            console.log(error)
            res.end("error")
        } else {
            console.log("success");
            res.end("success")
        }
    })
});

// 拦截来自所有前端的静态请求，都去static这个文件里去找寻找文件，放到最后
server.use(expressStatic(__dirname + '/static'))

server.listen(port);
console.log(`开放了${port}`)